package com.union.core.information.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hnjowe.webdb.DBOperation;
import com.union.core.information.dao.AnnouncementDao;
import com.union.core.information.model.Announcement;

public class AnnouncementDaoImpl implements AnnouncementDao{
	private Announcement announcement;
	private DBOperation opera;
	private static Logger log = Logger.getLogger(AnnouncementDaoImpl.class.getName());
	public AnnouncementDaoImpl(){
		
	}
	
	public List<Announcement> queryList(int announcement_id,String announcement_name,String announcement_content,String join_date1,
			String join_date2,String add_user,String add_time,int org_id,String paramName, String paramValue, String orderByName,int start,int pageSize){
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 
		String orderbySql = ""; //排序条件
		
		if (announcement_id!=0) sqlstr1 = " and announcement_id="+announcement_id;
		if (announcement_name!=null) sqlstr2 = " and announcement_name='"+announcement_name+"'";
		if (announcement_content!=null) sqlstr3 = " and announcement_content='"+announcement_content+"'";
		if (add_user!=null) sqlstr4 = " and add_user='"+add_user+"'";
		if (add_time!=null) sqlstr5 = " and add_time='"+add_time+"'";
		if (join_date1 != null && join_date2 != null){
			sqlstr6 = " and add_time between DATE_FORMAT('"+join_date1+"','%Y-%m-%d') and DATE_FORMAT('"+join_date2+"','%Y-%m-%d') ";
		}
		if(org_id!=0){
			sqlstr8 =" and receive like '%"+org_id+"%'";
		}
		if (paramName != null && paramValue != null) {
			if(paramName.equals("announcement_id")) sqlstr7 = " and announcement_id = "+paramValue;
			if(paramName.equals("announcement_name")) sqlstr7 = " and announcement_name like '%"+paramValue+"%'";
			if(paramName.equals("announcement_content")) sqlstr7 = " and announcement_content like '%"+paramValue+"%'";
			if(paramName.equals("add_user")) sqlstr7 = " and add_user like '%"+paramValue+"%'";
			if(paramName.equals("add_time")) sqlstr7 = " and add_time like '%"+paramValue+"%'";
		}

		if (orderByName != null&& !"".equals(orderByName)) {
			String str[] = orderByName.split("-");			
			if (str[1].equals("UP"))
				orderbySql = " order by " + str[0];
			if (str[1].equals("DOWN"))
				orderbySql = " order by " + str[0] + " desc";
		} else {
			orderbySql = " order by announcement_id asc";
		}
		
		List<Announcement> announcementList = new ArrayList<Announcement>();
		
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "announcement_id,announcement_name,announcement_content,start_date,end_date,add_user," 
					+ "add_time,update_user,update_time" 
					+ " from announcement where announcement_id>0" + sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 
					+ sqlstr7 + sqlstr8 +orderbySql  
			        + " LIMIT ?,?";
			opera.prepStatment(sql);
			 opera.setInt(1, start);
			 opera.setInt(2, pageSize);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				announcement = new Announcement();
				announcement.setAnnouncement_id(res.getInt(1));
				announcement.setAnnouncement_name(res.getString(2));
				announcement.setAnnouncement_content(res.getString(3));
				announcement.setStart_date(res.getString(4));
				announcement.setEnd_date(res.getString(5));
				announcement.setAdd_user(res.getString(6));
				announcement.setAdd_time(res.getString(7));
				announcement.setUpdate_user(res.getString(8));
				announcement.setUpdate_time(res.getString(9));
              
				announcementList.add(i, announcement);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
		return announcementList;
	}
	
	public int quertListCount(int announcement_id,String announcement_name,String announcement_content,String join_date1,
			String join_date2,String add_user,String add_time,int org_id, String paramName, String paramValue){
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件7
		
		if (announcement_id!=0) sqlstr1 = " and announcement_id="+announcement_id;
		if (announcement_name!=null) sqlstr2 = " and announcement_name='"+announcement_name+"'";
		if (announcement_content!=null) sqlstr3 = " and announcement_content='"+announcement_content+"'";
		if (add_user!=null) sqlstr4 = " and add_user='"+add_user+"'";
		if (add_time!=null) sqlstr5 = " and add_time='"+add_time+"'";
		if (join_date1 != null && join_date2 != null){
			sqlstr6 = " and add_time between DATE_FORMAT('"+join_date1+"','%Y-%m-%d') and DATE_FORMAT('"+join_date2+"','%Y-%m-%d') ";
		}
		if(org_id!=0){
			sqlstr8 =" and receive like '%"+org_id+"%'";
		}
		
		if (paramName != null && paramValue != null) {
			if(paramName.equals("announcement_id")) sqlstr7 = " and announcement_id = "+paramValue;
			if(paramName.equals("announcement_name")) sqlstr7 = " and announcement_name like '%"+paramValue+"%'";
			if(paramName.equals("announcement_content")) sqlstr7 = " and announcement_content like '%"+paramValue+"%'";
			if(paramName.equals("add_user")) sqlstr7 = " and add_user like '%"+paramValue+"%'";
			if(paramName.equals("add_time")) sqlstr7 = " and add_time like '%"+paramValue+"%'";
		}
		
		int totalSize = 0;

		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(*) as totalSize "
						+ "from announcement "
						+ "where announcement_id>0"+ sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 +sqlstr7+sqlstr8 ;
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	
	public Announcement query(int announcement_id){
		Announcement announcement = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select "
				+ "announcement_id,announcement_name,announcement_content,start_date,end_date,add_user," 
				+ "add_time,update_user,update_time,receive" 
				+ " from announcement"
				+ " where announcement_id=?";
			opera.prepStatment(sql);
			opera.setInt(1, announcement_id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				announcement = new Announcement();
				announcement.setAnnouncement_id(res.getInt(1));
				announcement.setAnnouncement_name(res.getString(2));
				announcement.setAnnouncement_content(res.getString(3));
				announcement.setStart_date(res.getString(4));
				announcement.setEnd_date(res.getString(5));
				announcement.setAdd_user(res.getString(6));
				announcement.setAdd_time(res.getString(7));
				announcement.setUpdate_user(res.getString(8));
				announcement.setUpdate_time(res.getString(9));
				announcement.setReceive(res.getString(10));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return announcement;
	}
	
	public Announcement queryNew(int org_id){
		Announcement announcement = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select "
				+ "announcement_id,announcement_name,announcement_content,start_date,end_date,add_user," 
				+ "add_time,update_user,update_time,receive" 
				+ " from announcement"
				+ " where announcement_id>0 and receive like '%"+org_id+"%' and SYSDATE() between start_date and end_date order by add_time desc limit 0,1";
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			while(res.next()){
				announcement = new Announcement();
				announcement.setAnnouncement_id(res.getInt(1));
				announcement.setAnnouncement_name(res.getString(2));
				announcement.setAnnouncement_content(res.getString(3));
				announcement.setStart_date(res.getString(4));
				announcement.setEnd_date(res.getString(5));
				announcement.setAdd_user(res.getString(6));
				announcement.setAdd_time(res.getString(7));
				announcement.setUpdate_user(res.getString(8));
				announcement.setUpdate_time(res.getString(9));
				announcement.setReceive(res.getString(10));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return announcement;
	}
	
	public boolean add(Announcement announcement){
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into announcement(announcement_name,announcement_content,start_date,end_date,add_user,add_time,receive)" 
				    + " values (?,?,?,?,?,SYSDATE(),?) ";
			opera.prepStatment(sql);
			opera.setString(1, announcement.getAnnouncement_name());
			opera.setString(2, announcement.getAnnouncement_content());
			opera.setString(3, announcement.getStart_date());
			opera.setString(4, announcement.getEnd_date());
			opera.setString(5, announcement.getAdd_user());
			opera.setString(6, announcement.getReceive());
			
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    }
	
	/**
	 * 
	 * @param announcement
	 * @return
	 */
	public boolean edit(Announcement announcement){
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update announcement "							
					+ "set announcement_name=?,announcement_content=?,start_date=?,end_date=?,update_user=?," 
				    + "update_time=SYSDATE()"
					+ " where announcement_id=?";
			opera.prepStatment(sql);
			opera.setString(1, announcement.getAnnouncement_name());
			opera.setString(2, announcement.getAnnouncement_content());
			opera.setString(3, announcement.getStart_date());
			opera.setString(4, announcement.getEnd_date());
			opera.setString(5, announcement.getUpdate_user());
			opera.setInt(6, announcement.getAnnouncement_id());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
	/**
	 * 
	 * @param announcement_id
	 * @return
	 */
	public Boolean delete(int announcement_id){
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from announcement "
					+ "where announcement_id=? ";
			opera.prepStatment(sql);
			opera.setInt(1, announcement_id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    	
    }
}